package cn.sfa.service.customer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.cache.CacheFactory;
import cn.sfa.cache.ICacheHelper;
import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.common.SaleHideCustomerCommonEntity;
import cn.sfa.entity.customer.SaleCustomerSee;
import cn.sfa.entity.customer.SaleHideCustomer;
/**
 * 
* 类名称：CustomerService   
* 类描述： 客户相关服务类
* 创建人：LLY   
* 创建时间：2012-2-15 下午11:12:18    
* 修改备注：   
* @version
 */
@Service
@Transactional
public class CustomerService {
	
	Logger logger = LoggerFactory.getLogger(CustomerService.class);
	ICacheHelper  cacheHelper = CacheFactory.CreateCacheHelper();
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	/**
	 * 添加客户
	 * @param customer
	 * @return
	 */
	public boolean addCustomer(SaleHideCustomer customer)
	{
		String sql="insert into salecustomer(salecustomerid,createdate,createuser,updatedate," +
				"updateuser,customername,CUSTOMERADDR,CUSTOMERCODE,CUSTOMERPHONE,CUSTOMERTYPE," +
				"CONTRACT,POSITION,MOBILE,QQ,EMAIL)"+
				" values(default,:createdate,:createuser,:updatedate,:updateuser," +
				":customername,:CUSTOMERADDR,:CUSTOMERCODE,:CUSTOMERPHONE,:CUSTOMERTYPE," +
				":CONTRACT,:POSITION,:MOBILE,:QQ,:EMAIL)";
		
		Map<String,Object> paramMap=new HashMap<String,Object>();
		paramMap.put("createdate",customer.getCreateDate());
		paramMap.put("createuser",CommonUtil.jugeAndFixNull(customer.getCreateUid()));
		paramMap.put("updatedate",customer.getUpdateDate());
		paramMap.put("updateuser",CommonUtil.jugeAndFixNull(customer.getUpdateId()));
		paramMap.put("customername",CommonUtil.jugeAndFixNull(customer.getCustomerName()));
		paramMap.put("CUSTOMERADDR",CommonUtil.jugeAndFixNull(customer.getCustomerAddr()));
		paramMap.put("CUSTOMERCODE",CommonUtil.jugeAndFixNull(customer.getCustomerCode()));
		paramMap.put("CUSTOMERPHONE",CommonUtil.jugeAndFixNull(customer.getCustomerPhone()));
		paramMap.put("CUSTOMERTYPE",CommonUtil.jugeAndFixNull(customer.getCustomerType()));
		paramMap.put("CONTRACT",CommonUtil.jugeAndFixNull(customer.getContract()));
		paramMap.put("POSITION",CommonUtil.jugeAndFixNull(customer.getPosition()));
		paramMap.put("MOBILE",CommonUtil.jugeAndFixNull(customer.getMobile()));
		paramMap.put("QQ",CommonUtil.jugeAndFixNull(customer.getQQ()));
		paramMap.put("EMAIL",CommonUtil.jugeAndFixNull(customer.getEmail()));
		
		int count=jdbcTemplate.update(sql, paramMap);
		if(count>0)
			return true;
		return false;
	}
	
	
	/**
	 * 添加客户跟进
	 * @param customer
	 * @return
	 */
	public boolean insertCustomerSee(SaleCustomerSee saleCustomerSee)
	{
		String sql="insert into salefollow(SALEFOLLOWID,createdate,createuser,updatedate," +
				"updateuser,userId,customerId,content,followtime," +
				"address,remark,status)"+
				" values(default,:createdate,:createuser,:updatedate,:updateuser," +
				":userId,:customerId,:content,:followTime," +
				":address,:remark,:status)";
		
		Map<String,Object> paramMap=new HashMap<String,Object>();
		paramMap.put("createdate",saleCustomerSee.getCreateDate());
		paramMap.put("createuser",CommonUtil.jugeAndFixNull(saleCustomerSee.getCreateUid()));
		paramMap.put("updatedate",saleCustomerSee.getUpdateDate());
		paramMap.put("updateuser",CommonUtil.jugeAndFixNull(saleCustomerSee.getUpdateId()));
		paramMap.put("userId",CommonUtil.jugeAndFixNull(saleCustomerSee.getUserId()));
		paramMap.put("customerId",CommonUtil.jugeAndFixNull(saleCustomerSee.getCustomerId()));
		paramMap.put("content",CommonUtil.jugeAndFixNull(saleCustomerSee.getVisitContent()));
		paramMap.put("followTime",saleCustomerSee.getVisitDate());
		paramMap.put("address",CommonUtil.jugeAndFixNull(saleCustomerSee.getAddress()));
		paramMap.put("remark",CommonUtil.jugeAndFixNull(saleCustomerSee.getRemark()));
		paramMap.put("status",CommonUtil.jugeAndFixNull(saleCustomerSee.getStatus()));
		
		int count=jdbcTemplate.update(sql, paramMap);
		if(count>0)
			return true;
		return false;
	}
	
	/**
	 * 关联客户信息
	 */
	public Pagination RelevanceSaleProduct(int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url// 分页url
	) {
		Map<String, Object> paraMap = new HashMap<String, Object>();
		String sqlStr = "select * from salecustomer";
		
		return new Pagination(sqlStr, paraMap, currentPage,
				numPerPage, url, jdbcTemplate);
	}
	
	/**
	 * 分页查询客户跟进
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param customer
	 * @return
	 */
	public Pagination searchCustomersFollow(int currentPage,//页码
			int numPerPage,//页尺寸
			String url,//分页url
			String userName,//分页url
			String customerName,//分页url
			String beginCreateDate,
			String endCreateDate
			) {
		
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select *,u.username,m.customername from salefollow s,saleuserslogin u,salecustomer m where 1=1 ");

		sqlStr.append("and s.userid in(select saleusersloginid from saleuserslogin where (:userName is null or username like :userName)) ");
		sqlStr.append("and s.userid = u.saleusersloginid and s.customerid = m.salecustomerid ");
		sqlStr.append("and s.customerid in (select customerid from salecustomer where (:customerName is null or customerName like :customerName)) ");
		sqlStr.append("and (:begincreatedate is null or s.CREATEDATE >= date_format(:begincreatedate, '%Y-%m-%d')) ");
        sqlStr.append("and (:endcreatedate is null or s.CREATEDATE <= date_format(concat(:endcreatedate,' 23:59:59'),'%Y/%m/%d %T')) ");
		Map<String,Object> paramMap = new HashMap<String,Object>(); 
		paramMap.put("userName", CommonUtil.isEmpty(userName)?null:"%"+userName+"%");
		paramMap.put("customerName", CommonUtil.isEmpty(customerName)?null:"%"+customerName+"%");
		paramMap.put("begincreatedate", CommonUtil.isEmpty(beginCreateDate)?null:beginCreateDate);
		paramMap.put("endcreatedate", CommonUtil.isEmpty(endCreateDate)?null:endCreateDate);
	 
		return new Pagination(sqlStr.toString(),paramMap,currentPage,numPerPage,url,jdbcTemplate); 
	}
	
	/**
	 * 分页查询客户
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param customer
	 * @return
	 */
	public Pagination searchCustomers(int currentPage,//页码
			int numPerPage,//页尺寸
			String url,//分页url
			SaleHideCustomer customer,
			String beginCreateDate,
			String endCreateDate
			) {
		
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append("select * from salecustomer s where 1=1 ");
		sqlStr.append("and (:customerName is null or s.customerName like :customerName) ");
		sqlStr.append("and (:contract is null or s.contract like :contract) ");
		sqlStr.append("and (:customerType is null or :customerType='ALL' or s.customerType=:customerType) ");
		sqlStr.append("and (:begincreatedate is null or s.CREATEDATE >= date_format(:begincreatedate, '%Y-%m-%d')) ");
        sqlStr.append("and (:endcreatedate is null or s.CREATEDATE <= date_format(concat(:endcreatedate,' 23:59:59'),'%Y/%m/%d %T')) ");
		Map<String,Object> paramMap = new HashMap<String,Object>(); 
		paramMap.put("customerName", CommonUtil.isEmpty(customer.getCustomerName())?null:"%"+customer.getCustomerName()+"%");
		paramMap.put("contract", CommonUtil.isEmpty(customer.getContract())?null:"%"+customer.getContract()+"%");
		paramMap.put("customerType", CommonUtil.isEmpty(customer.getCustomerType())?null:customer.getCustomerType());
		paramMap.put("begincreatedate", CommonUtil.isEmpty(beginCreateDate)?null:beginCreateDate);
		paramMap.put("endcreatedate", CommonUtil.isEmpty(endCreateDate)?null:endCreateDate);
	 
		return new Pagination(sqlStr.toString(),paramMap,currentPage,numPerPage,url,jdbcTemplate); 
	}
	
	 /**
	  * delete
	  * TODO
	  * @author: LiXiang
	  * @param saleHideCustomerId
	  * @return
	  */
	public boolean deleteCustomerById(String saleHideCustomerId) {
		String sql="delete from salecustomer where salecustomerid=:saleHideCustomerId";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("saleHideCustomerId", saleHideCustomerId);
		int count=jdbcTemplate.update(sql, map);
		if(count>0)
		{
			return true;
		}
		return false;
	}
	
	/**
	 * delete
	 * TODO
	 * @author: LiXiang
	 * @param id
	 * @return
	 */
	public boolean deleteCustomerFollowById(String id) {
		String sql="delete from salefollow where salefollowid=:id";
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("id", id);
		int count=jdbcTemplate.update(sql, map);
		if(count>0)
		{
			return true;
		}
		return false;
	}
	
	/**
	 * 根据客户ＩＤ查找客户
	 * @param saleHideCustomerId
	 * @return
	 */
	public SaleHideCustomer getCustomer(String saleHideCustomerId) {
		String sql = "select * from salecustomer where salecustomerid=:saleHideCustomerId ";
		final SaleHideCustomer customer=new SaleHideCustomer();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("saleHideCustomerId",saleHideCustomerId);
		jdbcTemplate.query(sql,paramMap ,new RowCallbackHandler() {
					
					@Override
					public void processRow(ResultSet rs) throws SQLException {
						
						customer.setSaleCustomerId(rs.getString("salecustomerid"));
						customer.setCreateDate(rs.getDate("createdate"));
						customer.setCreateUid(rs.getString("createuser"));
						customer.setUpdateDate(rs.getDate("updatedate"));
						customer.setUpdateId(rs.getString("updateuser"));
						customer.setCustomerName(rs.getString("customername"));
						customer.setCustomerAddr(rs.getString("customeraddr"));
						customer.setCustomerType(rs.getString("customertype"));
						customer.setContract(rs.getString("contract"));
						customer.setCustomerCode(rs.getString("customercode"));
						customer.setCustomerPhone(rs.getString("customerphone"));
						customer.setEmail(rs.getString("email"));
						customer.setMobile(rs.getString("mobile"));
						customer.setPosition(rs.getString("position"));
						customer.setQQ(rs.getString("qq"));
					}
						
					});
		return customer;
	}
	
	
	/**
	 * 根据客户ＩＤ查找客户跟进
	 * @param id
	 * @return
	 */
	public SaleCustomerSee getSaleCustomerSeeByID(String id) {
		String sql = "select * from salefollow where SALEFOLLOWID=:id ";
		final SaleCustomerSee customer=new SaleCustomerSee();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("id",id);
		jdbcTemplate.query(sql,paramMap ,new RowCallbackHandler() {
			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				customer.setSaleCustomerSeeId(rs.getString("SALEFOLLOWID"));
				customer.setCreateDate(rs.getDate("createdate"));
				customer.setCreateUid(rs.getString("createuser"));
				customer.setUpdateDate(rs.getDate("updatedate"));
				customer.setUpdateId(rs.getString("updateuser"));
				customer.setCustomerId(rs.getString("customerId"));
				customer.setUserId(rs.getString("userId"));
				customer.setAddress(rs.getString("address"));
				customer.setVisitContent(rs.getString("content"));
				customer.setStatus(rs.getString("status"));
				customer.setVisitDate(rs.getDate("followtime"));
			}
			
		});
		return customer;
	}
	
	/**
	 * 编辑客户跟进
	 * @param customer
	 * @return
	 */
	public boolean updateCustomerFollow(SaleCustomerSee customer) {
		String sql="update salefollow set  "+
				"updatedate=:updatedate,"+
				"updateuser=:updateuser,"+
				"customerId=:customerId,"+
				"CONTENT=:CONTENT,"+
				"FOLLOWTIME=:FOLLOWTIME,"+
				"ADDRESS=:ADDRESS,"+
				"REMARK=:REMARK,"+
				"STATUS=:STATUS"+
		" where SALEFOLLOWID=:SALEFOLLOWID";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("SALEFOLLOWID",CommonUtil.jugeAndFixNull(customer.getSaleCustomerSeeId()));
		paramMap.put("updatedate",customer.getUpdateDate());
		paramMap.put("updateuser",CommonUtil.jugeAndFixNull(customer.getUpdateId()));
		paramMap.put("customerId",CommonUtil.jugeAndFixNull(customer.getCustomerId()));
		paramMap.put("CONTENT",CommonUtil.jugeAndFixNull(customer.getVisitContent()));
		paramMap.put("FOLLOWTIME",customer.getVisitDate());
		paramMap.put("ADDRESS",CommonUtil.jugeAndFixNull(customer.getAddress()));
		paramMap.put("REMARK",CommonUtil.jugeAndFixNull(customer.getRemark()));
		paramMap.put("STATUS",CommonUtil.jugeAndFixNull(customer.getStatus()));
		int count=jdbcTemplate.update(sql, paramMap);
		if(count>0)
			return true;
		return false;
	}
	
	/**
	 * 编辑客户信息
	 * @param customer
	 * @return
	 */
	public boolean updateCustomer(SaleHideCustomer customer) {
		String sql="update salecustomer set  "+
				"updatedate=:updatedate,"+
				"updateuser=:updateuser,"+
				"customername=:customername,"+
				"CUSTOMERADDR=:CUSTOMERADDR,"+
				"CUSTOMERCODE=:CUSTOMERCODE,"+
				"CUSTOMERPHONE=:CUSTOMERPHONE,"+
				"CUSTOMERTYPE=:CUSTOMERTYPE,"+
				"CONTRACT=:CONTRACT,"+
				"POSITION=:POSITION,"+
				"MOBILE=:MOBILE,"+
				"QQ=:QQ,"+
				"EMAIL=:EMAIL"+
		" where salecustomerid=:salecustomerid";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("salecustomerid",CommonUtil.jugeAndFixNull(customer.getSaleCustomerId()));
		paramMap.put("updatedate",customer.getUpdateDate());
		paramMap.put("updateuser",CommonUtil.jugeAndFixNull(customer.getUpdateId()));
		paramMap.put("customername",CommonUtil.jugeAndFixNull(customer.getCustomerName()));
		paramMap.put("CUSTOMERADDR",CommonUtil.jugeAndFixNull(customer.getCustomerAddr()));
		paramMap.put("CUSTOMERCODE",CommonUtil.jugeAndFixNull(customer.getCustomerCode()));
		paramMap.put("CUSTOMERPHONE",CommonUtil.jugeAndFixNull(customer.getCustomerPhone()));
		paramMap.put("CUSTOMERTYPE",CommonUtil.jugeAndFixNull(customer.getCustomerType()));
		paramMap.put("CONTRACT",CommonUtil.jugeAndFixNull(customer.getContract()));
		paramMap.put("POSITION",CommonUtil.jugeAndFixNull(customer.getPosition()));
		paramMap.put("MOBILE",CommonUtil.jugeAndFixNull(customer.getMobile()));
		paramMap.put("QQ",CommonUtil.jugeAndFixNull(customer.getQQ()));
		paramMap.put("EMAIL",CommonUtil.jugeAndFixNull(customer.getEmail()));
		int count=jdbcTemplate.update(sql, paramMap);
		if(count>0)
			return true;
		return false;
	}
	
	  
	
	/**
	 * 获取客户详情
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param customerName
	 * @return
	 */
	public Pagination getCustomerDetail(int currentPage, int numPerPage,
			String url, String customerName) {
		String sql = " select  * from table(pkg_sfa_Customer.getCustomerDetail(:customerName))";
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		paramMap.put("customerName", CommonUtil.jugeAndFixNull(customerName));
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}
	//end  add by baoqiaolin  with 2012-3-20  
	
}
